package com.chen;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.*;

public class TestJdbc {

    public static void main(String[] args) {
        // 数据库连接URL，格式为：jdbc:子协议:子名称
        String jdbcUrl = "jdbc:mysql://localhost:3306/xk2309?serverTimezone=UTC";
        String username = "root";
        String password = "root";

        try {
            int pageSize=4;//规定的每页条数
            int recordNum=pageSize;//用于<PAGE的显示>

            // 加载并注册JDBC驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立数据库连接
            Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            Statement st = conn.createStatement();
            ResultSet resultSet = st.executeQuery("select count(*) as rowCount from admin where username='sc'");
            resultSet.next();
            int rowNum = resultSet.getInt("rowCount");//数据条数
            String sql= "select username,phone,address from admin  where username='sc'";
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();//字段数
            System.out.println("columnCount = " + columnCount);
            StringBuffer sBuf=new StringBuffer();

            int totalPage=0;//总页数,用于<PAGE>循环的次数

            if (rowNum>0&&rowNum<=pageSize){
                totalPage=1;
                recordNum=rowNum;//如果只有一页，page中的recordNum等于实际记录数，而不是pageSize
            }else{
                totalPage=rowNum/pageSize;
            }
            int lastPageSize=0;//最后一页的条数
            int pageNum=totalPage;//用于<Page_NUM>的显示
            //如果数据条数不为0，并且数据条数大于规定的每页条数，并且数据不能被每页条数整除
            if (rowNum>0&&rowNum>pageSize&&rowNum%pageSize!=0){
                lastPageSize=rowNum%pageSize;
                pageNum++;//总页数加1
            }

            System.out.println("totalPage = " + totalPage);


            sBuf.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>").append("\n");
            sBuf.append("<DATAFILE>").append("\n");
            sBuf.append("\t<DATAHEAD>").append("\n");
            sBuf.append("\t\t<DATA_NAME>");
            sBuf.append("</DATA_NAME>").append("\n");

            sBuf.append("\t\t<DATA_TYPE>");
            sBuf.append("</DATA_TYPE>").append("\n");

            sBuf.append("\t\t</ROW_NUM>");
            sBuf.append(rowNum);
            sBuf.append("<ROW_NUM>").append("\n");

            sBuf.append("\t\t</PAGE_NUM>");
            sBuf.append(pageNum);
            sBuf.append("<PAGE_NUM>").append("\n");

            sBuf.append("\t\t</FIELD_NUM>");
            sBuf.append(columnCount);
            sBuf.append("<FIELD_NUM>").append("\n");

            sBuf.append("\t\t<FIELDS>").append("\n");
            sBuf.append("\t\t</FIELDS>").append("\n");

            sBuf.append("\t</DATAHEAD>").append("\n");

            sBuf.append("<DATABODY>").append("\n");
            //如果查询记录为0
            if (rowNum>0){
                for (int j = 1; j <= totalPage; j++) {
                    sBuf.append("<PAGE PAGENO='"+j+"'"+" RECORDNUM='"+recordNum+"'>").append("\n");
                    for (int k = 0; k < recordNum; k++) {
                        if (rs.next()){
                            for (int i = 1; i <= columnCount; i++) {
                                String columnName = rsmd.getColumnName(i);
                                String value =rs.getString(i);
                                if (null==value){
                                    value="";
                                }
                                System.out.println("columnName = "+columnName);
                                System.out.println("value = " + value);
                                sBuf.append(value).append("|+|");
                            }
                            sBuf.append("|-|");
                            sBuf.append("\n");
                        }
                    }
                    sBuf.append("</PAGE>").append("\n");
                }
                //如果页数不能被整除，拼接最后一页数据
                if (lastPageSize>0){
                    sBuf.append("<PAGE PAGENO='"+(totalPage+1)+"'"+" RECORDNUM='"+lastPageSize+"'>").append("\n");
                    for (int k = 0; k < lastPageSize; k++) {
                        if (rs.next()){
                            for (int i = 1; i <= columnCount; i++) {
                                String columnName = rsmd.getColumnName(i);
                                String value =rs.getString(i);
                                if (null==value){
                                    value="";
                                }
//                                System.out.println("columnName = "+columnName);
//                                System.out.println("value = " + value);
                                sBuf.append(value).append("|+|");
                            }
                            sBuf.append("|-|");
                            sBuf.append("\n");
                        }
                    }
                    sBuf.append("</PAGE>").append("\n");
                }
            }
            sBuf.append("</DATABODY>").append("\n");
            sBuf.append("</DATAFILE>");
            String fileName="MYSQL2.xml";

            FileOutputStream fos = new FileOutputStream("D:\\"+fileName);
            fos.write(sBuf.toString().getBytes(StandardCharsets.UTF_8));
            fos.flush();
            fos.close();
            // 关闭数据库连接
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC驱动未找到");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
